import FileSaver from "file-saver";
const Excel = require("exceljs");
 
var setMerge = function (luckyMerge = {}, worksheet) {
  const mergearr = Object.values(luckyMerge);
  mergearr.forEach(function (elem) {
    // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    worksheet.mergeCells(
      elem.r + 1,
      elem.c + 1,
      elem.r + elem.rs,
      elem.c + elem.cs
    );
  });
};
 
var setBorder = function (luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) return;
  // console.log('luckyBorderInfo', luckyBorderInfo)
  luckyBorderInfo.forEach(function (elem) {
    // 现在只兼容到borderType 为range的情况
 
    if (elem.rangeType === "range") {
      const border = borderConvert(elem.borderType, elem.style, elem.color);
      const rang = elem.range[0];
      // console.log('range', rang)
      const row = rang.row;
      const column = rang.column;
      for (let i = row[0] + 1; i < row[1] + 2; i++) {
        for (let y = column[0] + 1; y < column[1] + 2; y++) {
          worksheet.getCell(i, y).border = border;
        }
      }
    }
    if (elem.rangeType === "cell") {
      const { col_index, row_index } = elem.value;
 
      const borderData = Object.assign({}, elem.value);
      delete borderData.col_index;
      delete borderData.row_index;
 
      const border = addborderToCell(borderData, row_index, col_index);
      worksheet.getCell(row_index + 1, col_index + 1).border = border;
    }
    // console.log(rang.column_focus + 1, rang.row_focus + 1)
    // worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
  });
};
 
/**
 * 转换日期为Excel序列号
 * @param {string|Date} dateValue - 日期值
 * @returns {number} Excel序列号
 */
const convertToExcelDate = (dateValue) => {
  if (!dateValue) return null;
 
  let date;
  if (dateValue instanceof Date) {
    date = dateValue;
  } else if (typeof dateValue === "string") {
    // 处理日期字符串，例如 "2025/1/20"
    date = new Date(dateValue);
  } else {
    return dateValue; // 如果不是日期则返回原值
  }
 
  if (isNaN(date.getTime())) {
    return dateValue; // 如果转换失败则返回原值
  }
 
  // Excel的起始日期是1900年1月1日
  const start = new Date(1900, 0, 1);
  const diff = date - start;
  const oneDay = 1000 * 60 * 60 * 24;
  const excelDate = diff / oneDay + 1;
 
  return excelDate;
};
 
/**
 * 判断是否为纯数字
 * @param {string} value - 要判断的值
 * @returns {boolean} 是否为纯数字
 */
const isNumeric = (value) => {
  if (typeof value !== "string") return false;
  return !isNaN(value) && !isNaN(parseFloat(value));
}
 
/**
 * 设置样式和值
 */
const setStyleAndValue = (cellArr, worksheet) => {
  if (!Array.isArray(cellArr)) return;
 
  cellArr.forEach((row, rowid) => {
    row.forEach((cell, columnid) => {
      if (!cell) return;
 
      const fill = fillConvert(cell.bg);
 
      const font = fontConvert(
        cell.ff,
        cell.fc,
        cell.bl,
        cell.it,
        cell.fs,
        cell.cl,
        cell.ul
      );
      const alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
 
      let value = "";
      if (cell.f) {
        // 尝试新的公式处理方式
        const formulaStr = cell.f.trim();
        value = { 
          formula: formulaStr.startsWith('=') ? formulaStr.substring(1) : formulaStr,
          result: cell.v,
          // date1904: false  // 添加这个属性试试
        }
      } else if (!cell.v && cell.ct && cell.ct.s) {
        value = cell.ct.s.reduce((acc, curr) => acc + curr.v, "");
      } else {
        // 处理时间格式
        if (
          cell.ct &&
          cell.ct.fa &&
          (cell.ct.fa.includes("m") ||
            cell.ct.fa.includes("d") ||
            cell.ct.fa.includes("y"))
        ) {
          // 如果是日期字符串，转换为Excel序列号
          const excelDate = convertToExcelDate(cell.v);
          if (excelDate !== null) {
            value = excelDate;
            worksheet.getCell(rowid + 1, columnid + 1).numFmt = 'yyyy-mm-dd';
          } else {
            value = cell.v;
          }
        } else {
          value = cell.v;
        }
      }
 
      if (value !== null) {
        // 只在 value 不为 null 时设置单元格值
        const letter = createCellPos(columnid);
        const target = worksheet.getCell(letter + (rowid + 1));
 
        if (Object.keys(fill).length > 0) {
          target.fill = fill;
        }
        target.font = font;
        target.alignment = alignment;
        if (value !== undefined && value !== null) {
      
          // 判断是否是数字 设置为数字格式
          target.value = isNumeric(value) ? value * 1 : value;
          // target.value = value
        }
      }
    });
  });
};
 
var setImages = function (imagesArr, worksheet, workbook) {
  if (typeof imagesArr !== "object") return;
  for (const key in imagesArr) {
    // console.log(imagesArr[key]);
    // 通过 base64  将图像添加到工作簿
    const myBase64Image = imagesArr[key].src;
    // 开始行 开始列 结束行 结束列
    const start = { col: imagesArr[key].fromCol, row: imagesArr[key].fromRow };
    const end = { col: imagesArr[key].toCol, row: imagesArr[key].toRow };
    const imageId = workbook.addImage({
      base64: myBase64Image,
      extension: "png",
    });
    worksheet.addImage(imageId, {
      tl: start,
      br: end,
      editAs: "oneCell",
    });
  }
};
 
var fillConvert = function (bg) {
  if (!bg) {
    return {};
  }
  // const bgc = bg.replace('#', '')
  const fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: bg.replace("#", "") },
  };
  return fill;
};
 
var fontConvert = function (
  ff = 0,
  fc = "#000000",
  bl = 0,
  it = 0,
  fs = 10,
  cl = 0,
  ul = 0
) {
  // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  const luckyToExcel = {
    0: "微软雅黑",
    1: "宋体（Song）",
    2: "黑体（ST Heiti）",
    3: "楷体（ST Kaiti）",
    4: "仿宋（ST FangSong）",
    5: "新宋体（ST Song）",
    6: "华文新魏",
    7: "华文行楷",
    8: "华文隶书",
    9: "Arial",
    10: "Times New Roman ",
    11: "Tahoma ",
    12: "Verdana",
    num2bl: function (num) {
      return num !== 0;
    },
  };
  // 出现Bug，导入的时候ff为luckyToExcel的val
 
  let colorValue = fc;
  if (fc.indexOf('rgb') !== -1) {
    // 处理 rgb 格式
    const rgb = fc.match(/\d+/g);
    if (rgb && rgb.length >= 3) {
      const [r, g, b] = rgb;
      colorValue = ((r << 16) | (g << 8) | b).toString(16).padStart(6, '0');
    }
  } else {
    // 处理十六进制格式
    colorValue = fc.replace("#", "");
  }
 
  const font = {
    name: typeof ff === "number" ? luckyToExcel[ff] : ff,
    family: 1,
    size: fs,
    color: { argb: 'FF' + colorValue },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(ul),
    strike: luckyToExcel.num2bl(cl),
  };
 
  return font;
};
 
var alignmentConvert = function (
  vt = "default",
  ht = "default",
  tb = "default",
  tr = "default"
) {
  // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  const luckyToExcel = {
    vertical: {
      0: "middle",
      1: "top",
      2: "bottom",
      default: "top",
    },
    horizontal: {
      0: "center",
      1: "left",
      2: "right",
      default: "left",
    },
    wrapText: {
      0: false,
      1: false,
      2: true,
      default: false,
    },
    textRotation: {
      0: 0,
      1: 45,
      2: -45,
      3: "vertical",
      4: 90,
      5: -90,
      default: 0,
    },
  };
 
  const alignment = {
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr],
  };
  return alignment;
};
 
var borderConvert = function (borderType, style = 1, color = "#000") {
  // 对应luckysheet的config中borderinfo的的参数
  if (!borderType) {
    return {};
  }
  const luckyToExcel = {
    type: {
      "border-all": "all",
      "border-top": "top",
      "border-right": "right",
      "border-bottom": "bottom",
      "border-left": "left",
    },
    style: {
      0: "none",
      1: "thin",
      2: "hair",
      3: "dotted",
      4: "dashDot", // 'Dashed',
      5: "dashDot",
      6: "dashDotDot",
      7: "double",
      8: "medium",
      9: "mediumDashed",
      10: "mediumDashDot",
      11: "mediumDashDotDot",
      12: "slantDashDot",
      13: "thick",
    },
  };
  const template = {
    style: luckyToExcel.style[style],
    color: { argb: color.replace("#", "") },
  };
  const border = {};
  if (luckyToExcel.type[borderType] === "all") {
    border["top"] = template;
    border["right"] = template;
    border["bottom"] = template;
    border["left"] = template;
  } else {
    border[luckyToExcel.type[borderType]] = template;
  }
  // console.log('border', border)
  return border;
};
 
function addborderToCell(borders) {
  const border = {};
  const luckyExcel = {
    type: {
      l: "left",
      r: "right",
      b: "bottom",
      t: "top",
    },
    style: {
      0: "none",
      1: "thin",
      2: "hair",
      3: "dotted",
      4: "dashDot", // 'Dashed',
      5: "dashDot",
      6: "dashDotDot",
      7: "double",
      8: "medium",
      9: "mediumDashed",
      10: "mediumDashDot",
      11: "mediumDashDotDot",
      12: "slantDashDot",
      13: "thick",
    },
  };
  // console.log('borders', borders)
  for (const bor in borders) {
    // console.log(bor)
    if (borders[bor].color.indexOf("rgb") === -1) {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color.replace("#", "") },
      };
    } else {
      border[luckyExcel.type[bor]] = {
        style: luckyExcel.style[borders[bor].style],
        color: { argb: borders[bor].color },
      };
    }
  }
 
  return border;
}
 
function createCellPos(n) {
  const ordA = "A".charCodeAt(0);
 
  const ordZ = "Z".charCodeAt(0);
  const len = ordZ - ordA + 1;
  let s = "";
  while (n >= 0) {
    s = String.fromCharCode((n % len) + ordA) + s;
 
    n = Math.floor(n / len) - 1;
  }
  return s;
}
 
/**
 * 列宽
 * @param columnWidth
 * @param worksheet
 */
var setColumnWidth = function (columnWidth, worksheet) {
  for (let key in columnWidth) {
    worksheet.getColumn(parseInt(key) + 1).width = columnWidth[key] / 7.5;
  }
};
 
/**
 * 行高
 * @param rowHeight
 * @param worksheet
 * @param excelType
 */
var setRowHeight = function (rowHeight, worksheet) {
  for (let key in rowHeight) {
    worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75;
  }
};
 
export var exportExcel = async function (luckysheet, value, isDown = false) {
  const workbook = new Excel.Workbook();
 
  luckysheet.forEach(function (table) {
    // 深拷贝数据以避免修改原始数据
    const tableData = JSON.parse(JSON.stringify(table.data));
    
    // 移除预处理公式的代码，让 Excel 自己处理公式格式
    if (tableData.length === 0) return true;
    
    const worksheet = workbook.addWorksheet(table.name);
    const borderInfo = (table.config && table.config.borderInfo) || {};
    const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
    const rowHeight = (table.config && table.config.rowlen) || {}; //行高
 
    // 3. 设置单元格合并, 边框, 样式, 值, 图片
    setColumnWidth(columnWidth, worksheet);
    //行高设置50导出后在ms-excel中打开显示25，在wps-excel中打开显示50这个bug不会修复
    setRowHeight(rowHeight, worksheet);
 
    setMerge((table.config && table.config.merge) || {}, worksheet);
    setBorder(borderInfo, worksheet);
    setStyleAndValue(tableData, worksheet);
    setImages(table.images, worksheet, workbook);
 
    return true;
  });
 
  let data = await workbook.xlsx.writeBuffer();
 
  const blob = new Blob([data], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8",
  });
 
  // 创建File对象
  const file = new File([blob], `${value}`, {
    type: blob.type,
    lastModified: new Date().getTime(),
  });
 
  if (isDown) return file;
 
  console.log("导出成功！");
  FileSaver.saveAs(blob, `${value}`);
 
  return workbook.xlsx.writeBuffer();
};